use master
go

create database bbs
go

use bbs
go

create table bbsUsers
(
	UID int identity(1,1) ,
	uName varchar(10) not null,
	uSex varchar(2) not null,
	uAge int not null,
	uPoint int not null 
)
--
alter table bbsUsers add constraint PK_bbsUser_UID primary key(UID)
--
alter table bbsUsers add constraint UK_bbsUser_uName unique(uName)
--
alter table bbsUsers add constraint CK_bbsUser_uSex check(uSex in('男','女'))
--
alter table bbsUsers add constraint CK_bbsUser_uAge check(uAge>=15 or uAge<=60)
--
alter table bbsUsers add constraint CK_bbsUser_uPoint check(uPoint>=0)
--

create table bbsSection
(
	sID int identity(1,1),
	sName varchar(10) not null,
	sUid int 
)

alter table bbsSection add constraint PK_bbsSection_sID primary key(sID)
--
alter table bbsSection add constraint FK_bbsSection_sUid foreign key(sUid) references bbsUsers(UID)



create  table bbsTopic
(
	tID int primary key identity(1,1),
	tUID int foreign key references bbsUsers(UID),
	tSID int foreign key references bbsSection(sID),
	tTItle varchar(100) not null,
	tMsg text not null,
	tTime datetime,
	tCount int
)


create table bbsReply
(
	rID INT primary key identity(1,1),
	rUID int foreign key references bbsUsers(UID),
	rTID int foreign key references bbsTopic(tID),
	rMsg text not null,
	rTime datetime
)

insert into bbsUsers values
('小雨点','女',20,0),
('逍遥','男',18,4),
('七年级生','男',19,2) 


select * into bbsPoint from bbsUsers

insert into bbsSection values
('技术交流',1),
('读书世界',3),
('生活百科',1),
('八卦区',3)

insert into bbsTopic values
(2,4,'今年夏天最流行什么','有谁知道今年夏天最流行什么呀?',2008-9-10,0),
(3,1,'.NET','与JAVA的区别是什么呀?',2008-9-1,2),
(1,3,'范跑跑','谁是范跑跑',2008-7-8,1)

insert into bbsReply values
(2,1,'不知道',2008-9-10),
(3,2,'不知道',2008-9-1),
(1,3,'不知道',2008-7-8)

select * from bbsReply  --回帖表
select * from bbsSection  --版块表
select * from bbsTopic  --主帖表
select * from  bbsUsers  --用户信息表

--在论坛数据库中完成以下题目
--1.查询出 每个版块 的 版主编号， 版主姓名 和版块名称
select sUid 版主编号,uName 版主姓名,sName 版块名称 from bbsSection
inner  join bbsUsers on bbsUsers.UID = bbsSection.sUid
  
--2.查询出主贴的发帖时间在2008-9-15以后的主贴的发帖人编号，发帖人姓名，帖子的标题，帖子的内容和发帖时间
select tUID 发帖人编号,uName 发帖人姓名,tTItle 帖子的标题 ,tMsg  帖子的内容,tTime  发帖时间 from bbsTopic
inner  join bbsUsers on bbsUsers.UID = bbsTopic.tUID  where tTime>'2008-9-15'


--3.查询出年龄在20以下的版主的编号，版主的名称和版块的名称
select sUid 版主编号,uName 版主姓名,sName 版块名称 from bbsSection
inner  join bbsUsers on bbsUsers.UID = bbsSection.sUid where uAge<20

--4.查询出回复数量最多的主贴的发帖人编号，发帖人姓名，主贴标题，主贴内容和回复数量
select rUID 发帖人编号 , uName 发帖人姓名 ,tTItle 主贴标题 , tMsg 主贴内容 , tCount 回复数量 from bbsReply
inner join bbsTopic on bbsTopic.tUID  = bbsReply.rUID  
inner join bbsUsers on bbsUsers.UID = bbsReply.rUID  where tCount=(select max(tCount) from bbsTopic)

--5.在主贴表中查询每个版块中每个用户的发帖总数
select tSID 版块 , uName 发帖人姓名 , count(*) from bbsTopic
inner join bbsUsers on bbsUsers.UID = bbsTopic.tUID 
inner join bbsSection  on bbsUsers.UID =bbsSection.sID group by tSID ,uName